package com.mmy.dao.plane;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.plane.TPlaneOrg;

public interface TPlaneOrgRepository extends JpaRepository<TPlaneOrg, Integer> {

	/**
	 * 获取最大值ID
	 * 
	 * @return 返回值说明 最大ID
	 * @author 创建人hanxl
	 * @date 创建日期 2021.03.17
	 */
	@Query(value = "SELECT MAX(iOrgID) FROM tPlaneOrg WHERE LEFT(iOrgID,2) = RIGHT(YEAR(NOW()),2)", nativeQuery = true)
	Object getMaxOrgID();

	/*
	 * 根据name查找
	 */
	@Query(value = "select *  from tPlaneOrg where cOrgName like %:strOrgName%", nativeQuery = true)
	List<TPlaneOrg> findByOrgName(@Param("strOrgName") String strOrgName);

	/**
	 * 新增
	 * 
	 * @return 返回值说明
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.03.17
	 */
	@Transactional
	@Modifying
	@Query(value = "INSERT INTO tPlaneOrg(iOrgID, cOrgName, cName, cTel, dCreateDate, cCreator, dModifyDate, cReviser, cMemo, cSpare1, cSpare2, cSpare3, cSpare4, cSpare5) VALUES (:orgID, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", nativeQuery = true)
	void save(@Param("orgID") int orgID);

	/*
	 * 根据id删除
	 */
	@Transactional
	@Modifying
	@Query(value = "delete from tPlaneOrg where tPlaneOrg.iOrgID in (:lstOrgID)", nativeQuery = true)
	void delPlaneOrg(@Param("lstOrgID") List<Integer> lstOrgID);
	/**
	 * 函数功能说明：后台 修改航空公司信息 查询该信息
	 *
	 * @param 参数说明 tPlaneOrg
	 * @return 返回值说明 jsp页面
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.03.19
	 */
	@Query(value = "select *  from tPlaneOrg where iOrgID = :iOrgID", nativeQuery = true)
	TPlaneOrg findByOrgID(@Param("iOrgID") int iOrgID);

	/**
	 * 后台航空公司信息管理中显示所有航空公司信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from tPlaneOrg where(:strPlaneOrgName = \"\" or cOrgName like %:strPlaneOrgName%)  "
			+ "and (:strName = \"\" or cName like %:strName%) "
			+ "and (:strTel = \"\" or cTel like %:strTel%) limit :startIndex,:count", nativeQuery = true)
	List<TPlaneOrg> findSearchALL(@Param("strPlaneOrgName") String strPlaneOrgName, @Param("strName") String strName,
			@Param("strTel") String strTel, @Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台航空公司信息管理中显示所有公司的航班信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.02.22
	 */
	@Query(value = "select count(*)  from tPlaneOrg where (:strPlaneOrgName = \"\" or cOrgName like %:strPlaneOrgName%) "
			+ "and (:strName = \"\" or cName like %:strName%) "
			+ "and (:strTel = \"\" or cTel like %:strTel%)", nativeQuery = true)
	int countSearchALL(@Param("strPlaneOrgName") String strPlaneOrgName, @Param("strName") String strName,
			@Param("strTel") String strTel);

}
